/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.JDBC;

import core.Book;
import core.Course;
import core.Program;
import core.School;
import core.User;
import db.interfaces.CourseDAO;
import java.sql.Connection;
import java.util.Collection;
/**
 *
 * @author Ahlberg
 * A class for fetching data from the schhol database.
 */
public class CourseJdbcDAO extends BaseJdbcDAO implements CourseDAO
{
    /**
     * 
     * @param con
     */
    public CourseJdbcDAO(Connection con)
    {
        super(con);
    }

    /**
     * 
     * @param id
     * @return
     */
    @Override
    public Course getCourseById(long id)
    {
        return super.getCourseById(id);
    }
    /**
     * 
     * @param beginsWith
     * @return
     */
    public Collection<Course> searchForCoursesByName(String beginsWith)
    {
        return search(beginsWith, "name");
    }
    /**
     * 
     * @param beginsWith
     * @return
     */
    public Collection<Course> searchForCoursesByCourseId(String beginsWith)
    {
        return search(beginsWith, "courseid");
    }
    /**
     *
     * @param beginsWith
     * @param fieldName
     * @param asc
     * @return
     */
    public Collection<Course> searchForCoursesByParameters(String beginsWith, String fieldName, boolean asc)
    {
        String sql = "";
        String sort = "ASC";
        if(!asc)
            sort = "DESC";
        if(fieldName.equals(""))
            sql = "SELECT * FROM Courses ORDER BY " + fieldName + " " + sort +";";
        else
            sql = "SELECT * FROM Courses WHERE " +
                "id LIKE '" + beginsWith + "%' OR " +
                "name LIKE '" + beginsWith + "%' OR " +
                "courseid LIKE '" + beginsWith + "%' ORDER BY " +
                fieldName + " " + sort +";";
        return createCourseCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param beginsWith
     * @param field
     * @return
     */
    private Collection<Course> search(String beginsWith, String field)
    {
        String sql = "SELECT * FROM Courses WHERE " + field + " LIKE '" + beginsWith + "%' ORDER BY " + field + " ASC;";
        return createCourseCollection(sqlExecuteQuery(sql));
    }

    /**
     * 
     * @param name
     * @return
     */
    public Collection<Course> getCoursesByName(String name)
    {
        if(name == null)
            return null;
        String sql = "SELECT * FROM Courses WHERE name='" + name + "' ORDER BY courseid ASC;";
        return createCourseCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param courseId
     * @return
     */
    public Collection<Course> getCoursesByCourseId(String courseId)
    {
        if(courseId == null)
            return null;
        String sql = "SELECT * FROM Courses WHERE courseid='" + courseId + "' ORDER BY name ASC;";
        return createCourseCollection(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @return
     */
    public Collection<Course> getAllCourses()
    {
        String sql = "SELECT * FROM Courses ORDER BY name ASC, courseid ASC;";
        return createCourseCollection(sqlExecuteQuery(sql));
    }

    /**
     * 
     * @param program
     * @return
     */
    public Collection<Course> getAllCoursesInProgram(Program program)
    {
        if(program == null)
            return null;
        String sql = "SELECT * FROM ProgramCourse WHERE program='" + program.getId() + "';";
        return createCourseCollection(createIdCollection(sqlExecuteQuery(sql), "course"));
    }
    /**
     * 
     * @param school
     * @return
     */
    public Collection<Course> getAllCoursesInSchool(School school)
    {
        if(school == null)
            return null;
        String sql = "SELECT * FROM SchoolCourse WHERE school='" + school.getId() + "';";
        return createCourseCollection(createIdCollection(sqlExecuteQuery(sql), "course"));
    }

    /**
     * 
     * @param user
     * @return
     */
    public Collection<Course> getAllCoursesInUser(User user)
    {
        if(user == null)
            return null;
        String sql = "SELECT * FROM UserCourse WHERE user = '" + user.getId() + "';";
        return createCourseCollection(createIdCollection(sqlExecuteQuery(sql), "course"));
    }

    /**
     * 
     * @param course
     * @param book
     * @return
     */
    public boolean addBookToCourse(Course course, Book book)
    {
        if(course == null || book == null)
            return false;
        String sql = "INSERT INTO CourseBook (course, book) " +
                           "VALUES ("
                           + "'" + course.getId() + "', "
                           + "'" + book.getId() + "');";
        return add(sql);
    }
    /**
     * 
     * @param course
     * @param book
     * @return
     */
    public boolean deleteBookFromCourse(Course course, Book book)
    {
        if(course == null || book == null)
            return false;
        String sql = "DELETE FROM CourseBook WHERE course = '" + course.getId() + "' AND book = '" + book.getId() + "';";
        return delete(sql);
    }

    /**
     * 
     * @param course
     * @return
     */
    @Override
    public boolean courseIsBound(Course course)
    {
        if(course == null)
            return false;
        return super.courseIsBound(course);
    }
    /**
     * 
     * @param course
     * @return
     */
    public boolean containsCourse(Course course)
    {
        if(course == null)
            return false;
        String sql = "SELECT * FROM Courses WHERE id='" + course.getId() + "';";
        return contains(sql);
    }
    /**
     * 
     * @param course
     * @return
     */
    public boolean addCourse(Course course)
    {
        if(course == null)
            return false;
        String sql = "INSERT INTO Courses (name, courseid, description, weblink) " +
                           "VALUES ("
                           + "'" + course.getName() + "', "
                           + "'" + course.getCourseId() + "', "
                           + "'" + course.getDescription() + "', "
                           + "'" + course.getWebLink() + "');";
        boolean b = add(sql);
        course.setId(getLatestAddedId("Courses"));
        return b;
    }
    /**
     * 
     * @param course
     * @return
     */
    public boolean editCourse(Course course)
    {
        if(course == null)
            return false;
        String sql = "UPDATE Courses SET  " +
                "name='" + course.getName() +
                "', description = '" + course.getDescription() +
                "', weblink = '" + course.getWebLink() +
                "', courseid = '" + course.getCourseId() +
                "' WHERE id='" + course.getId() + "';";
        return edit(sql);
    }
    /**
     * 
     * @param course
     * @return
     */
    public boolean deleteCourse(Course course)
    {
        if(course == null)
            return false;
        deleteCourseFromAllBindings(course);
        String sql = "DELETE FROM Courses WHERE id = ' " + course.getId() + "'";
        return delete(sql);
    }
    /**
     * 
     * @param course
     */
    private void deleteCourseFromAllBindings(Course course)
    {
        if(course == null)
            return;
        removeCoursefromUsers(course);

        String sql = "DELETE FROM ProgramCourse WHERE course = ' " + course.getId() + "'";
        delete(sql);

        sql = "DELETE FROM SchoolCourse WHERE course = ' " + course.getId() + "'";
        delete(sql);
    }
}
